﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Runda.CMS.Common.Utility;
using System.Data.Common;
using Runda.CMS.Common.Entity;

namespace Runda.CMS.Dal
{
    public partial class CtrtBillDal
    {
        public DataTable QueryBill(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder("select * from (select sum(iTaxUnitPrice) as iTaxUnitPriceTotal,  sum(iNatMoney) as iNatMoneyTotal,sum(iNatTax) as iNatTaxTotal, sum(iNatSum) as iNatSumTotal,sum(iExchSum) as iExchSumTotal ,cBVCode,dDate,idDueDte,cCusCode,(sum(iNatSum) -sum(iExchSum)) as SubstrctTotal from Cms_Ctrt_Bill group by cBVCode,dDate,idDueDte,cCusCode) AS Z where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetCustomerBillForAgeStat(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from vw_BillAge where ((finenum=0 and rewardNum>0) or  (finenum>0 and rewardNum=0)) {0}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
        public DataTable QueryBills(string whereClause,string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select a.* from vw_Bills a
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //查询发票明细
        public DataTable QuerySalesBills1(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select billGUID,billUnit,BudgetUnit,cBVCode,Sales,DepartmentName,SalesModelType,SalesType
            ,dDate,cMemo,iQuantity,iTaxUnitPrice,iNatMoney,iNatSum,case when Sourcetype=3 then '手工录人' else '发票导入' end Sourcetype
            ,CategoryName,instrumentName,instrumentStandard,customerName,cast(billGUID as varchar(50))+'|'+cBVCode+'|'+cast(iNatSum as varchar(20)) as rowkeyinfo
            ,Cost,Cost1,Cost2,Cost3,SaleAmount,SaleAmount1,SaleAmount2,SaleAmount3,SaleAmount4
            ,(Cost+Cost2+Cost3) as TotalCost
            ,(SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) as TotalSaleAmount
            ,((SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4)-(Cost+Cost2+Cost3)) as TotalRevenue
            ,(SaleAmount-Cost) as Revenue,case when SaleAmount <> 0 then (SaleAmount-Cost)/SaleAmount else 0 end as RevenueRate
            ,case when (SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) <> 0 then ((SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4)-(Cost+Cost2+Cost3))/(SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) else 0 end as TotalRevenueRate
from vw_BG_Bill_query 
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


        //获得销售发票
        public DataTable QuerySalesBills(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select a.billGUID,a.billUnit,a.BudgetUnit,a.cBVCode,a.dDate,a.cMemo,a.iQuantity,a.iTaxUnitPrice,a.iNatMoney,a.iNatSum,case when a.Sourcetype=3 then '手工录人' else '发票导入' end Sourcetype,cc.CategoryName,a.instrumentName,a.instrumentStandard,c.customerName,cast(a.billGUID as varchar(50))+'|'+a.cBVCode+'|'+cast(a.iNatSum as varchar(20)) as rowkeyinfo from Cms_BG_Bill a
inner join vw_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=left(a.cCategoryCode,6)
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


        public DataTable QuerySalesBills(string whereClause, string orderBy)
        {
            string sqlSb = @"select a.*,cc.CategoryName,c.customerName,cast(a.billGUID as varchar(50))+'|'+a.cBVCode+'|'+cast(a.iNatSum as varchar(20)) as rowkeyinfo from Cms_BG_Bill a
inner join vw_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=left(a.cCategoryCode,6)
where 1=1 {0} ORDER BY {1}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause,orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable SumSalesZK(string whereClause)
        {
            string sqlSb = @"select 
                        BillUnit,Yearmonth
                        ,SUM(iNatSum) as iNatSum
                        ,SUM(unAssoicateAmount) as unAssoicateAmount
                        ,SUM(AssoicateAmount) as AssoicateAmount
                        from
                        (
                        select BillUnit,CONVERT(char(7),ddate,121) as Yearmonth
                        , case when iNatSum_adjust = 0 then iNatSum else 0 end as unAssoicateAmount
                        , case when iNatSum_adjust = 0 then 0 else iNatSum_adjust end as AssoicateAmount
                        ,iNatSum
                         from vw_Salebills_Zk
                        where cCusCode not in(select customerCode from cms_Bg_InnerCustomer) and InvName <> '现金折扣' 
                         ) s 
                         group by BillUnit,Yearmonth order by BillUnit,Yearmonth ";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable GetSalesZKBills(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select customerName,cBVCode,dDate,sum(iNatSum) as iNatSum from (select a.*,c.customerName from vw_Salebills_Zk a
inner join vw_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
where 1=1 {0}) s group by customerName,cBVCode,dDate";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable SaleBillsStat(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select BillUnit,BudgetUnit,case when SourceType = 3 then '财务调整' else DepartmentName end as DepartmentName,case when SourceType = 3 then '财务调整' else sales end as sales,CustomerName,FactoryName,categoryNameEx,CategoryCode,sum(iNatSum) as TotaliNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '01' then iNatSum end,0))  as m01iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '02' then iNatSum end,0))  as m02iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '03' then iNatSum end,0))  as m03iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '04' then iNatSum end,0))  as m04iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '05' then iNatSum end,0))  as m05iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '06' then iNatSum end,0))  as m06iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '07' then iNatSum end,0))  as m07iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '08' then iNatSum end,0))  as m08iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '09' then iNatSum end,0))  as m09iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '10' then iNatSum end,0))  as m10iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '11' then iNatSum end,0))  as m11iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '12' then iNatSum end,0))  as m12iNatSum
from vw_BG_Bill 
where iNatSum<>0 {0}
group by BillUnit,BudgetUnit,DepartmentName,sales,CustomerName,FactoryName,categoryNameEx,SourceType,CategoryCode";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);

            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);
            dbCommand.CommandTimeout = 0;

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable SaleBillsStat1(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select *
	                ,(m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum) as TotaliNatSum
	                ,(m01Cost+m02Cost+m03Cost+m04Cost+m05Cost+m06Cost+m07Cost+m08Cost+m09Cost+m10Cost+m11Cost+m12Cost) as TotalCost
	                , (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum
	                -(m01Cost+m02Cost+m03Cost+m04Cost+m05Cost+m06Cost+m07Cost+m08Cost+m09Cost+m10Cost+m11Cost+m12Cost)) as TotalRevenue
	                ,(m01iNatSum - m01Cost) as m01Revenue
	                ,(m02iNatSum - m02Cost) as m02Revenue
	                ,(m03iNatSum - m03Cost) as m03Revenue
	                ,(m04iNatSum - m04Cost) as m04Revenue
	                ,(m05iNatSum - m05Cost) as m05Revenue
	                ,(m06iNatSum - m06Cost) as m06Revenue
	                ,(m07iNatSum - m07Cost) as m07Revenue
	                ,(m08iNatSum - m08Cost) as m08Revenue
	                ,(m09iNatSum - m09Cost) as m09Revenue
	                ,(m10iNatSum - m10Cost) as m10Revenue
	                ,(m11iNatSum - m11Cost) as m11Revenue
	                ,(m12iNatSum - m12Cost) as m12Revenue
	                ,case when (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum)!=0
			                then (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum
	                -(m01Cost+m02Cost+m03Cost+m04Cost+m05Cost+m06Cost+m07Cost+m08Cost+m09Cost+m10Cost+m11Cost+m12Cost)) / (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum)
	                else 0 end as TotalRevenueRate
	                ,case when m01iNatSum!=0 then (m01iNatSum - m01Cost) / m01iNatSum else 0 end as m01RevenueRate
	                ,case when m02iNatSum!=0 then (m02iNatSum - m02Cost) / m02iNatSum else 0 end as m02RevenueRate
	                ,case when m03iNatSum!=0 then (m03iNatSum - m03Cost) / m03iNatSum else 0 end as m03RevenueRate
	                ,case when m04iNatSum!=0 then (m04iNatSum - m04Cost) / m04iNatSum else 0 end as m04RevenueRate
	                ,case when m05iNatSum!=0 then (m05iNatSum - m05Cost) / m05iNatSum else 0 end as m05RevenueRate
	                ,case when m06iNatSum!=0 then (m06iNatSum - m06Cost) / m06iNatSum else 0 end as m06RevenueRate
	                ,case when m07iNatSum!=0 then (m07iNatSum - m07Cost) / m07iNatSum else 0 end as m07RevenueRate
	                ,case when m08iNatSum!=0 then (m08iNatSum - m08Cost) / m08iNatSum else 0 end as m08RevenueRate
	                ,case when m09iNatSum!=0 then (m09iNatSum - m09Cost) / m09iNatSum else 0 end as m09RevenueRate
	                ,case when m10iNatSum!=0 then (m10iNatSum - m10Cost) / m10iNatSum else 0 end as m10RevenueRate
	                ,case when m11iNatSum!=0 then (m11iNatSum - m11Cost) / m11iNatSum else 0 end as m11RevenueRate
	                ,case when m12iNatSum!=0 then (m12iNatSum - m12Cost) / m12iNatSum else 0 end as m12RevenueRate
                from
                (
                select BillUnit,BudgetUnit,case when SourceType = 3 then '财务调整' else DepartmentName end as DepartmentName,case when SourceType = 3 then '财务调整' else sales end as sales,CustomerName,FactoryName,categoryNameEx,CategoryCode
                ,sum(isnull(case billMonthex when 1 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m01iNatSum
                ,sum(isnull(case billMonthex when 2 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m02iNatSum
                ,sum(isnull(case billMonthex when 3 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m03iNatSum
                ,sum(isnull(case billMonthex when 4 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m04iNatSum
                ,sum(isnull(case billMonthex when 5 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m05iNatSum
                ,sum(isnull(case billMonthex when 6 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m06iNatSum
                ,sum(isnull(case billMonthex when 7 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m07iNatSum
                ,sum(isnull(case billMonthex when 8 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m08iNatSum
                ,sum(isnull(case billMonthex when 9 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m09iNatSum
                ,sum(isnull(case billMonthex when 10 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m10iNatSum
                ,sum(isnull(case billMonthex when 11 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m11iNatSum
                ,sum(isnull(case billMonthex when 12 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m12iNatSum
                ,sum(isnull(case billMonthex when 1 then Cost+Cost2+Cost3 else 0 end,0))  as m01Cost
                ,sum(isnull(case billMonthex when 2 then Cost+Cost2+Cost3 else 0 end,0))  as m02Cost
                ,sum(isnull(case billMonthex when 3 then Cost+Cost2+Cost3 else 0 end,0))  as m03Cost
                ,sum(isnull(case billMonthex when 4 then Cost+Cost2+Cost3 else 0 end,0))  as m04Cost
                ,sum(isnull(case billMonthex when 5 then Cost+Cost2+Cost3 else 0 end,0))  as m05Cost
                ,sum(isnull(case billMonthex when 6 then Cost+Cost2+Cost3 else 0 end,0))  as m06Cost
                ,sum(isnull(case billMonthex when 7 then Cost+Cost2+Cost3 else 0 end,0))  as m07Cost
                ,sum(isnull(case billMonthex when 8 then Cost+Cost2+Cost3 else 0 end,0))  as m08Cost
                ,sum(isnull(case billMonthex when 9 then Cost+Cost2+Cost3 else 0 end,0))  as m09Cost
                ,sum(isnull(case billMonthex when 10 then Cost+Cost2+Cost3 else 0 end,0))  as m10Cost
                ,sum(isnull(case billMonthex when 11 then Cost+Cost2+Cost3 else 0 end,0))  as m11Cost
                ,sum(isnull(case billMonthex when 12 then Cost+Cost2+Cost3 else 0 end,0))  as m12Cost
                from vw_BG_Bill 
                where 1=1 {0}
                group by BillUnit,BudgetUnit,DepartmentName,sales,CustomerName,FactoryName,categoryNameEx,SourceType,CategoryCode
                ) s";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);

            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);
            dbCommand.CommandTimeout = 0;

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


        public DataTable SalesRpt_102a(string WhereClause,string beginMonth,string endMonth)
        {
            string sqlSb = @"select a.* from
                (
                select FactoryName,categorynameex
                 ,sum(budget.rdBudgetAmount) as rdBudgetAmount,sum(sale.rdSaleAmount) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(sale.rdSaleAmount) / sum(budget.rdBudgetAmount),4) end as rdrate
                 ,sum(budget.kxBudgetAmount) as kxBudgetAmount,sum(sale.kxSaleAmount) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(sale.kxSaleAmount) / sum(budget.kxBudgetAmount),4) end as kxrate
                 ,sum(budget.yxBudgetAmount) as yxBudgetAmount,sum(sale.yxSaleAmount) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(sale.yxSaleAmount) / sum(budget.yxBudgetAmount),4) end as yxrate
                 ,sum(budget.hzBudgetAmount) as hzBudgetAmount,sum(sale.hzSaleAmount) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(sale.hzSaleAmount) / sum(budget.hzBudgetAmount),4) end as hzrate
                 ,sum(budget.hcBudgetAmount) as hcBudgetAmount,sum(sale.hcSaleAmount) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(sale.hcSaleAmount) / sum(budget.hcBudgetAmount),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_SalesBills_rpt) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                where cc.orderby is not null
                group by FactoryName,categorynameex
                ) a
                inner join 
                (
                select distinct categoryNameex,orderby from Cms_Ctrt_InstrumentCategory
                ) b on b.categoryNameex = a.categorynameex
                order by b.orderby";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        public DataTable SaleBillsStatByCategory(string WhereClause, Guid UserID)
        {
            string sqlSb = @"select FactoryName,categoryName,sum(iNatSum) as TotaliNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '01' then iNatSum end,0))  as m01iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '02' then iNatSum end,0))  as m02iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '03' then iNatSum end,0))  as m03iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '04' then iNatSum end,0))  as m04iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '05' then iNatSum end,0))  as m05iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '06' then iNatSum end,0))  as m06iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '07' then iNatSum end,0))  as m07iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '08' then iNatSum end,0))  as m08iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '09' then iNatSum end,0))  as m09iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '10' then iNatSum end,0))  as m10iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '11' then iNatSum end,0))  as m11iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '12' then iNatSum end,0))  as m12iNatSum
from vw_BG_Bill 
where 1=1 {0}
group by FactoryName,categoryName";
            string sqlStr = string.Format(sqlSb.ToString(), UserID, WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable GetSaleRevenue(string whereClause)
        {
            string sqlSb = @"select isnull(sum(SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4),0) as Amount
,isnull(SUM(cost+Cost2+Cost3),0) as Cost
from vw_BG_Bill 
where cCusCode not in(select customerCode from cms_Bg_InnerCustomer) {0}
";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable CalcSaleBillsAmount(string whereClause)
        {
            string sqlSb = @"select sum(iNatSum) as TotalAmount
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '01' then iNatSum end,0))  as m01iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '02' then iNatSum end,0))  as m02iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '03' then iNatSum end,0))  as m03iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '04' then iNatSum end,0))  as m04iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '05' then iNatSum end,0))  as m05iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '06' then iNatSum end,0))  as m06iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '07' then iNatSum end,0))  as m07iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '08' then iNatSum end,0))  as m08iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '09' then iNatSum end,0))  as m09iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '10' then iNatSum end,0))  as m10iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '11' then iNatSum end,0))  as m11iNatSum
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '12' then iNatSum end,0))  as m12iNatSum
from vw_BG_Bill 
where iNatSum<>0 {0}
";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable CalcSaleBillsAmount1(string whereClause)
        {
            string sqlSb = @"select 
                isnull(SUM(SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4) ,0)as TotalAmount
                ,isnull(SUM(Cost+Cost2+Cost3),0) as TotalCost
                ,isnull(sum(isnull(case billMonthex when 1 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m01iNatSum
                ,isnull(sum(isnull(case billMonthex when 2 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m02iNatSum
                ,isnull(sum(isnull(case billMonthex when 3 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m03iNatSum
                ,isnull(sum(isnull(case billMonthex when 4 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m04iNatSum
                ,isnull(sum(isnull(case billMonthex when 5 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m05iNatSum
                ,isnull(sum(isnull(case billMonthex when 6 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m06iNatSum
                ,isnull(sum(isnull(case billMonthex when 7 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m07iNatSum
                ,isnull(sum(isnull(case billMonthex when 8 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m08iNatSum
                ,isnull(sum(isnull(case billMonthex when 9 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)) ,0) as m09iNatSum
                ,isnull(sum(isnull(case billMonthex when 10 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m10iNatSum
                ,isnull(sum(isnull(case billMonthex when 11 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m11iNatSum
                ,isnull(sum(isnull(case billMonthex when 12 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0)),0)  as m12iNatSum
                ,isnull(sum(isnull(case billMonthex when 1 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m01Cost
                ,isnull(sum(isnull(case billMonthex when 2 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m02Cost
                ,isnull(sum(isnull(case billMonthex when 3 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m03Cost
                ,isnull(sum(isnull(case billMonthex when 4 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m04Cost
                ,isnull(sum(isnull(case billMonthex when 5 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m05Cost
                ,isnull(sum(isnull(case billMonthex when 6 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m06Cost
                ,isnull(sum(isnull(case billMonthex when 7 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m07Cost
                ,isnull(sum(isnull(case billMonthex when 8 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m08Cost
                ,isnull(sum(isnull(case billMonthex when 9 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m09Cost
                ,isnull(sum(isnull(case billMonthex when 10 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m10Cost
                ,isnull(sum(isnull(case billMonthex when 11 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m11Cost
                ,isnull(sum(isnull(case billMonthex when 12 then Cost+Cost2+Cost3 else 0 end,0)),0)  as m12Cost
                from vw_BG_Bill 
                where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable CalcbillAmount(string WhereClause)
        {
            StringBuilder sqlSb = new StringBuilder(@"select sum(iNatSum) as iNatSum from vw_BG_Bill a
left join BudgetUnitRef b on b.customerCode = a.cCusCode and a.cCategoryCode=b.categoryCode and a.BudgetUnit = b.billUnit
 where a.cCusCode not in(select customerCode from cms_Bg_InnerCustomer) and categoryName not like '%折扣' and a.sourcetype <> 3 and pipei = 0 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);



            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }
        public DataTable CalcBudgetAmount(string WhereClause)
        {
            StringBuilder sqlSb = new StringBuilder(@"select sum(TotalAmount) as TotalAmount 
            from vw_InBudgetNotInSaleBill v
            inner join Cms_Ctrt_Customer cust with(nolock) on cust.CustomerCode = v.CustomerCode and (cust.company = v.companyName or cust.CustomerCode = 'xzkh')
            inner join Cms_Ctrt_InstrumentCategory cate with(nolock) on cate.categoryCode = v.CategoryCode
            where 1=1  {0}");
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);



            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        //有销售无预算
        public DataTable SaleBillNotinBudget(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select a.billunit+'|'+a.cCusCode+'|'+cCategoryCode as rowInfo,a.sales,a.billunit,customerName,categoryName,SUM(iNatsum) as iNatSum from vw_BG_Bill a
left join BudgetUnitRef b on b.customerCode = a.cCusCode and a.cCategoryCode=b.categoryCode and a.BudgetUnit = b.billUnit
 where a.cCusCode not in(select customerCode from cms_Bg_InnerCustomer) and categoryName not like '%折扣' and a.sourcetype <> 3 and pipei = 0 {0}
group by a.sales,a.billunit,customerName,categoryName,a.billunit+'|'+a.cCusCode+'|'+cCategoryCode having SUM(iNatsum) <> 0";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
        //有预算无销售
        public DataTable BudgetNotSaleBill(string whereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select 
v.*,cust.CustomerName,cate.categoryName,v.companyname+'|'+v.customercode+'|'+v.categorycode+'|'+v.sales as rowInfoex
from vw_InBudgetNotInSaleBill v
inner join vw_Customer cust with(nolock) on cust.CustomerCode = v.CustomerCode and (cust.company = v.companyName or cust.CustomerCode = 'xzkh')
inner join Cms_Ctrt_InstrumentCategory cate with(nolock) on cate.categoryCode = v.CategoryCode
where 1=1 {0} ";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, orderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //预算达成-按产品 当期 汇总
        public DataTable sumSalesRpt_102a(string sqlwhere,string strCondition, string beginMonth, string endMonth)
        {
            string sql = @"select 
                sum(isnull(budget.rdBudgetAmount,0)) as rdBudgetAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.rdSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)),4) end as rdrate
                 ,sum(isnull(budget.kxBudgetAmount,0)) as kxBudgetAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.kxSaleAmount,0)) / sum(isnull(budget.kxBudgetAmount,0)),4) end as kxrate
                 ,sum(isnull(budget.yxBudgetAmount,0)) as yxBudgetAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.yxSaleAmount,0)) / sum(isnull(budget.yxBudgetAmount,0)),4) end as yxrate
                 ,sum(isnull(budget.hzBudgetAmount,0)) as hzBudgetAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hzSaleAmount,0)) / sum(isnull(budget.hzBudgetAmount,0)),4) end as hzrate
                 ,sum(isnull(budget.hcBudgetAmount,0)) as hcBudgetAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.hcBudgetAmount,0)),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth,companyname,sales
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and BillMonth >= @beginMonth and BillMonth <= @endMonth
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= @beginMonth and BillMonth <= @endMonth
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                where 1=1 {1}
                ";
            sql = string.Format(sql, sqlwhere,strCondition);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@beginMonth", System.Data.DbType.String, beginMonth);
            CurrentDatabase.AddInParameter(dbCommand, "@endMonth", System.Data.DbType.String, endMonth);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }

        //销售模式-产品 当期 汇总
        public DataTable sumSalesRpt_108(string sqlwhere, string strCondition, string beginMonth, string endMonth)
        {
            string sql = @"select 
                sum(isnull(sale.rdSaleAmount1,0)) as rdSaleAmount1,sum(isnull(sale.rdSaleAmount2,0)) as rdSaleAmount2,sum(isnull(sale.rdSaleAmount3,0)) as rdSaleAmount3,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount
                 ,sum(isnull(sale.kxSaleAmount1,0)) as kxSaleAmount1,sum(isnull(sale.kxSaleAmount2,0)) as kxSaleAmount2,sum(isnull(sale.kxSaleAmount3,0)) as kxSaleAmount3,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount
                 ,sum(isnull(sale.yxSaleAmount1,0)) as yxSaleAmount1,sum(isnull(sale.yxSaleAmount2,0)) as yxSaleAmount2,sum(isnull(sale.yxSaleAmount3,0)) as yxSaleAmount3,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount
                 ,sum(isnull(sale.hzSaleAmount1,0)) as hzSaleAmount1,sum(isnull(sale.hzSaleAmount2,0)) as hzSaleAmount2,sum(isnull(sale.hzSaleAmount3,0)) as hzSaleAmount3,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount
                 ,sum(isnull(sale.hcSaleAmount1,0)) as hcSaleAmount1,sum(isnull(sale.hcSaleAmount2,0)) as hcSaleAmount2,sum(isnull(sale.hcSaleAmount3,0)) as hcSaleAmount3,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount
                 ,sum(isnull(sale.rdSaleAmount1,0))+sum(isnull(sale.kxSaleAmount1,0))+sum(isnull(sale.yxSaleAmount1,0))+sum(isnull(sale.hzSaleAmount1,0))+sum(isnull(sale.hcSaleAmount1,0)) as TotalSaleAmount1
                 ,sum(isnull(sale.rdSaleAmount2,0))+sum(isnull(sale.kxSaleAmount2,0))+sum(isnull(sale.yxSaleAmount2,0))+sum(isnull(sale.hzSaleAmount2,0))+sum(isnull(sale.hcSaleAmount2,0)) as TotalSaleAmount2
                 ,sum(isnull(sale.rdSaleAmount3,0))+sum(isnull(sale.kxSaleAmount3,0))+sum(isnull(sale.yxSaleAmount3,0))+sum(isnull(sale.hzSaleAmount3,0))+sum(isnull(sale.hcSaleAmount3,0)) as TotalSaleAmount3
                 ,sum(isnull(sale.rdSaleAmount,0))+sum(isnull(sale.kxSaleAmount,0))+sum(isnull(sale.yxSaleAmount,0))+sum(isnull(sale.hzSaleAmount,0))+sum(isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                from vw_InstrumentCategory_Stat cc
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount1) as rdSaleAmount1
	                ,SUM(rdSaleAmount2) as rdSaleAmount2
	                ,SUM(rdSaleAmount3) as rdSaleAmount3
	                ,SUM(rdSaleAmount1)+SUM(rdSaleAmount2)+SUM(rdSaleAmount3) as rdSaleAmount
	                ,SUM(kxSaleAmount1) as kxSaleAmount1
	                ,SUM(kxSaleAmount2) as kxSaleAmount2
	                ,SUM(kxSaleAmount3) as kxSaleAmount3
	                ,SUM(kxSaleAmount1)+SUM(kxSaleAmount2)+SUM(kxSaleAmount3) as kxSaleAmount
	                ,SUM(yxSaleAmount1) as yxSaleAmount1
	                ,SUM(yxSaleAmount2) as yxSaleAmount2
	                ,SUM(yxSaleAmount3) as yxSaleAmount3
	                ,SUM(yxSaleAmount1)+SUM(yxSaleAmount2)+SUM(yxSaleAmount3) as yxSaleAmount
	                ,SUM(hzSaleAmount1) as hzSaleAmount1
	                ,SUM(hzSaleAmount2) as hzSaleAmount2
	                ,SUM(hzSaleAmount3) as hzSaleAmount3
	                ,SUM(hzSaleAmount1)+SUM(hzSaleAmount2)+SUM(hzSaleAmount3) as hzSaleAmount
	                ,SUM(hcSaleAmount1) as hcSaleAmount1
	                ,SUM(hcSaleAmount2) as hcSaleAmount2
	                ,SUM(hcSaleAmount3) as hcSaleAmount3
	                ,SUM(hcSaleAmount1)+SUM(hcSaleAmount2)+SUM(hcSaleAmount3) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	               ,case when companyName='润达' and SalesModelType = '直销' then iNatSum else 0 end as rdSaleAmount1
	                ,case when companyName='润达' and SalesModelType = '经销' then iNatSum else 0 end as rdSaleAmount2
	                ,case when companyName='润达' and SalesModelType = '三产' then iNatSum else 0 end as rdSaleAmount3
	                ,case when companyName='康祥' and SalesModelType = '直销' then iNatSum else 0 end as kxSaleAmount1
	                ,case when companyName='康祥' and SalesModelType = '经销' then iNatSum else 0 end as kxSaleAmount2
	                ,case when companyName='康祥' and SalesModelType = '三产' then iNatSum else 0 end as kxSaleAmount3
	                ,case when companyName='益信' and SalesModelType = '直销' then iNatSum else 0 end as yxSaleAmount1
	                ,case when companyName='益信' and SalesModelType = '经销' then iNatSum else 0 end as yxSaleAmount2
	                ,case when companyName='益信' and SalesModelType = '三产' then iNatSum else 0 end as yxSaleAmount3
	                ,case when companyName='惠中' and SalesModelType = '直销' then iNatSum else 0 end as hzSaleAmount1
	                ,case when companyName='惠中' and SalesModelType = '经销' then iNatSum else 0 end as hzSaleAmount2
	                ,case when companyName='惠中' and SalesModelType = '三产' then iNatSum else 0 end as hzSaleAmount3
	                ,case when companyName='华臣' and SalesModelType = '直销' then iNatSum else 0 end as hcSaleAmount1
	                ,case when companyName='华臣' and SalesModelType = '经销' then iNatSum else 0 end as hcSaleAmount2
	                ,case when companyName='华臣' and SalesModelType = '三产' then iNatSum else 0 end as hcSaleAmount3
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= @beginMonth and BillMonth <= @endMonth
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                where 1=1 {1}
                ";
            sql = string.Format(sql, sqlwhere, strCondition);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@beginMonth", System.Data.DbType.String, beginMonth);
            CurrentDatabase.AddInParameter(dbCommand, "@endMonth", System.Data.DbType.String, endMonth);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }

        //预算达成-按产品 全年 汇总
        public DataTable sumSalesRpt_102b(string sqlwhere, string strCondition, string beginMonth, string endMonth,string strYear)
        {
            string sql = @"select 
                sum(isnull(budget.rdBudgetAmount,0)) as rdBudgetAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.rdSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)),4) end as rdrate
                 ,sum(isnull(budget.kxBudgetAmount,0)) as kxBudgetAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.kxSaleAmount,0)) / sum(isnull(budget.kxBudgetAmount,0)),4) end as kxrate
                 ,sum(isnull(budget.yxBudgetAmount,0)) as yxBudgetAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.yxSaleAmount,0)) / sum(isnull(budget.yxBudgetAmount,0)),4) end as yxrate
                 ,sum(isnull(budget.hzBudgetAmount,0)) as hzBudgetAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hzSaleAmount,0)) / sum(isnull(budget.hzBudgetAmount,0)),4) end as hzrate
                 ,sum(isnull(budget.hcBudgetAmount,0)) as hcBudgetAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.hcBudgetAmount,0)),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth,companyname,sales
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and left(BillMonth,4) = @strYear
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= @beginMonth and BillMonth <= @endMonth
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                where 1=1 {1}
                ";
            sql = string.Format(sql, sqlwhere, strCondition);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@beginMonth", System.Data.DbType.String, beginMonth);
            CurrentDatabase.AddInParameter(dbCommand, "@endMonth", System.Data.DbType.String, endMonth);
            CurrentDatabase.AddInParameter(dbCommand, "@strYear", System.Data.DbType.String, strYear);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }


        //销售 汇总 按产品
        public DataTable sumSalesRpt_103(string sqlwhere, string strCondition)
        {
            string sql = @"select 
                    SUM(Mon1Natsum) as Mon1Natsum
                    ,sum(Mon2Natsum) as Mon2Natsum
                    ,sum(Mon3Natsum) as Mon3Natsum
                    ,sum(Mon4Natsum) as Mon4Natsum
                    ,sum(Mon5Natsum) as Mon5Natsum
                    ,sum(Mon6Natsum) as Mon6Natsum
                    ,sum(Mon7Natsum) as Mon7Natsum
                    ,sum(Mon8Natsum) as Mon8Natsum
                    ,sum(Mon9Natsum) as Mon9Natsum
                    ,sum(Mon10Natsum) as Mon10Natsum
                    ,sum(Mon11Natsum) as Mon11Natsum
                    ,sum(Mon12Natsum) as Mon12Natsum
                    ,(sum(ISNULL(Mon1Natsum,0))+sum(ISNULL(Mon2Natsum,0))+sum(ISNULL(Mon3Natsum,0))+sum(ISNULL(Mon4Natsum,0))
                    +sum(ISNULL(Mon5Natsum,0))+sum(ISNULL(Mon6Natsum,0))+sum(ISNULL(Mon7Natsum,0))+sum(ISNULL(Mon8Natsum,0))
                    +sum(ISNULL(Mon9Natsum,0))+sum(ISNULL(Mon10Natsum,0))+sum(ISNULL(Mon11Natsum,0))+sum(ISNULL(Mon12Natsum,0))) as TotalNatsum
                    from
                    (
                    select
                    a.FactoryName,a.categoryNameex,b.* from
                    vw_InstrumentCategory_Stat a
                     left join
                    (
                    select CategoryCode
                    ,sum(Mon1Natsum) as Mon1Natsum
                    ,sum(Mon2Natsum) as Mon2Natsum
                    ,sum(Mon3Natsum) as Mon3Natsum
                    ,sum(Mon4Natsum) as Mon4Natsum
                    ,sum(Mon5Natsum) as Mon5Natsum
                    ,sum(Mon6Natsum) as Mon6Natsum
                    ,sum(Mon7Natsum) as Mon7Natsum
                    ,sum(Mon8Natsum) as Mon8Natsum
                    ,sum(Mon9Natsum) as Mon9Natsum
                    ,sum(Mon10Natsum) as Mon10Natsum
                    ,sum(Mon11Natsum) as Mon11Natsum
                    ,sum(Mon12Natsum) as Mon12Natsum
                     from vw_SaleBills_1
                     where 1=1 {0}
                     group by CategoryCode
                     ) b on a.categoryCode = b.categorycode
                    where 1=1 {1}
                    ) s
                    ";
            sql = string.Format(sql, sqlwhere, strCondition);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }

        //上年同期比较 汇总
        public DataTable sumSalesRpt_104(string sqlwhere, string strCondition, string beginMonth, string endMonth)
        {
            string sql = @"select 
                sum(isnull(presales.rdpresalesAmount,0)) as rdpresalesAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(presales.rdpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.rdSaleAmount,0))-sum(isnull(presales.rdpresalesAmount,0))) / sum(isnull(presales.rdpresalesAmount,0)),4) end as rdrate
                 ,sum(isnull(presales.kxpresalesAmount,0)) as kxpresalesAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(presales.kxpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.kxSaleAmount,0))-sum(isnull(presales.kxpresalesAmount,0))) / sum(isnull(presales.kxpresalesAmount,0)),4) end as kxrate
                 ,sum(isnull(presales.yxpresalesAmount,0)) as yxpresalesAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(presales.yxpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.yxSaleAmount,0))-sum(isnull(presales.yxpresalesAmount,0))) / sum(isnull(presales.yxpresalesAmount,0)),4) end as yxrate
                 ,sum(isnull(presales.hzpresalesAmount,0)) as hzpresalesAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(presales.hzpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.hzSaleAmount,0))-sum(isnull(presales.hzpresalesAmount,0))) / sum(isnull(presales.hzpresalesAmount,0)),4) end as hzrate
                 ,sum(isnull(presales.hcpresalesAmount,0)) as hcpresalesAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(presales.hcpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.hcSaleAmount,0))-sum(isnull(presales.hcpresalesAmount,0))) / sum(isnull(presales.hcpresalesAmount,0)),4) end as hcrate
                 ,sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0)) as TotalpresalesAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0))=0 then 0
                 else round((sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0))-sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0))) / sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat_ex cc
                left join 
                (
	                select categorycode
	                ,SUM(rdpresalesamount) as rdpresalesamount
	                ,SUM(kxpresalesamount) as kxpresalesamount
	                ,SUM(yxpresalesamount) as yxpresalesamount
	                ,SUM(hzpresalesamount) as hzpresalesamount
	                ,SUM(hcpresalesamount) as hcpresalesamount
	                from
	                (
	                select categorycode,sMonth,companyname
	                ,case when companyname='润达' then salesamount else 0 end as rdpresalesamount
	                ,case when companyname='康祥' then salesamount else 0 end as kxpresalesamount
	                ,case when companyname='益信' then salesamount else 0 end as yxpresalesamount
	                ,case when companyname='惠中' then salesamount else 0 end as hzpresalesamount
	                ,case when companyname='华臣' then salesamount else 0 end as hcpresalesamount
	                from cms_bg_salebills_2011) s
                    where 1=1 {0} and sMonth >= substring(@beginMonth,6,2) and sMonth <= substring( @endMonth,6,2)
	                group by categorycode
                    ) presales on cc.categorycode = presales.categorycode 
                    left join
                    (	
	                    select categorycode
	                    ,SUM(rdSaleAmount) as rdSaleAmount
	                    ,SUM(kxSaleAmount) as kxSaleAmount
	                    ,SUM(yxSaleAmount) as yxSaleAmount
	                    ,SUM(hzSaleAmount) as hzSaleAmount
	                    ,SUM(hcSaleAmount) as hcSaleAmount
	                    from
	                    (
	                    select categorycode,BillMonth,customercode,companyname
	                    ,case when companyname='润达' then iNatSum else 0 end as rdSaleAmount
	                    ,case when companyname='康祥' then iNatSum else 0 end as kxSaleAmount
	                    ,case when companyname='益信' then iNatSum else 0 end as yxSaleAmount
	                    ,case when companyname='惠中' then iNatSum else 0 end as hzSaleAmount
	                    ,case when companyname='华臣' then iNatSum else 0 end as hcSaleAmount
	                    from vw_SalesBills_rpt) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= @beginMonth and BillMonth <= @endMonth
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                where 1=1 {1}
                ";
            sql = string.Format(sql, sqlwhere, strCondition);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@beginMonth", System.Data.DbType.String, beginMonth);
            CurrentDatabase.AddInParameter(dbCommand, "@endMonth", System.Data.DbType.String, endMonth);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }

        //
        public DataTable QueryBill(string whereClause)
        {
            StringBuilder sqlSb = new StringBuilder("select * from (select isnull(sum(iTaxUnitPrice),0) as iTaxUnitPriceTotal,  isnull(sum(iNatMoney),0) as iNatMoneyTotal, isnull(sum(iNatTax),0) as iNatTaxTotal, isnull(sum(iNatSum),0) as iNatSumTotal,isnull(sum(iExchSum),0) as iExchSumTotal ,cBVCode,dDate,idDueDte,cCusCode,(isnull(sum(iNatSum),0) -isnull(sum(iExchSum),0)) as SubstrctTotal from Cms_Ctrt_Bill where 1=1 {0} group by cBVCode,dDate,idDueDte,cCusCode) AS Z ");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            
            return ds.Tables[0];
        } 


        public DataTable GetCustomerPerformance(string WhereClause,Guid UserID, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from (
select b.cCusCode,c.CustomerName,u.Username,max(vw.Age) as Age,sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill 
group by  cCusCode,cBVCode
) B 
left join
( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer c on b.cCusCode = c.CustomerCode
inner join Cms_Sys_User u with(nolock) on u.UserID = c.Salesman
left join vw_CustomerAge vw on vw.CustomerID = c.CustomerID
where c.Salesman = '{0}'
or c.Salesman in ( select UserID from Cms_Sys_UserDepartment where DepartmentID in (select DepartmentID from Cms_Sys_UserDepartment where UserID in
(
select userid from Cms_Sys_User where IsManager = 1  and UserID = '{0}'
)))
group by b.cCusCode,c.CustomerName,u.Username
) AS Z where 1=1 {1}";
            string sqlStr = string.Format(sqlSb.ToString(),UserID, WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetCustomerPerformance(string WhereClause, string ufAccountno, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from (
select b.cCusCode,c.CustomerName,u.userName,max(vw.Age) as Age,sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable,SUM(isnull(c1.AccountPayableEx,0)) as AccountPayableEx
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
where cBVCode like '%{0}'
group by  cCusCode,cBVCode
) B 
left join
( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer c on b.cCusCode = c.CustomerCode
left join Cms_Sys_User u on u.UserID = c.Salesman
left join vw_CustomerAge vw on vw.CustomerID = c.CustomerID
group by b.cCusCode,c.CustomerName,u.userName
) AS Z where 1=1 {1}";
            string sqlStr = string.Format(sqlSb.ToString(), ufAccountno, WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable CalcCustomerPerformance(string WhereClause, Guid UserID)
        {
            string sqlSb = @"select * from (
select count(b.cCusCode) as custs,sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
group by  cCusCode,cBVCode
) B 
 left join 
( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
join Cms_Ctrt_Customer c on b.cCusCode = c.CustomerCode
where c.Salesman = '{0}'
) AS Z where 1=1 {1}";
            string sqlStr = string.Format(sqlSb.ToString(), UserID, WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable CalcCustomerPerformance(string WhereClause, string ufaccountno)
        {
            string sqlSb = @"select * from (
select count(b.cCusCode) as custs,sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable,SUM(isnull(c1.AccountPayableEx,0)) as AccountPayableEx
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
where cBVCode like '%{0}'
group by  cCusCode,cBVCode
) B 
 left join ( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
join Cms_Ctrt_Customer c on b.cCusCode = c.CustomerCode
 where 1=1 {1}
) AS Z";
            string sqlStr = string.Format(sqlSb.ToString(), ufaccountno, WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

            return ds.Tables[0];
        }

        public DataTable GetBillPerformance(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from (
select a.cCuscode,a.cBVCode,a.dDate,alerttype,a.iNatSum,a.iExchSum,isnull(b.Fine,0) as Fine,isnull(b.Reward,0) as Reward,b.Year,b.Month,cast(b.Year as varchar(4))+'-'+cast(b.Month as varchar(2)) as Yearmonth,a.AccountPayable as AccountPayable from 
(select cCuscode,cBVCode,dDate,alerttype,sum(iNatSum) as iNatSum,sum(iExchSum) as iExchSum,sum(iNatSum-iExchSum) as AccountPayable from vw_Invoice  where 1=1 {0} group by cCuscode,cBVCode,dDate,alerttype) a 
left join Cms_Arm_UserPerformance b
on a.cBVCode = b.cBVCode
) AS Z where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY alertType desc,{2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetCustomerAgeStat(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from vw_CustomerAgeStat where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


        public DataTable QueryUserPerformance(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"
select C.UserID,C.UserName, sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable,SUM(isnull(c1.AccountPayableEx,0)) as AccountPayableEx
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
group by  cCusCode,cBVCode
) B 
left join ( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer d on b.cCusCode = d.CustomerCode
left join Cms_Sys_User C on d.Salesman= C.UserID
where 1=1 {0}
group by C.UserName,C.UserID
";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause,OrderBy);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
        public DataTable QueryUserPerformance(string strAccountno,string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"
select C.UserID,C.UserName, sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable
from (
select cCusCode,cBVCode,sum(iNatSum) as iNatSum,sum(iExchSum) as iExchSum,sum(iNatSum-iExchSum) as AccountPayable
from Cms_Ctrt_Bill
where cAccountNo in({0})
group by  cCusCode,cBVCode
) B 
left join ( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer d on b.cCusCode = d.CustomerCode
left join Cms_Sys_User C on d.Salesman= C.UserID
where 1=1 {1}
group by C.UserName,C.UserID
";

            string sqlStr = string.Format(sqlSb.ToString(), strAccountno,WhereClause, OrderBy);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable QueryUserPerformanceSummary(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"
select (sum(Reward) + sum(Fine)) as  totalReward,Month,Year,UserID from Cms_Arm_UserPerformance
where 1=1 {0}
group by Month,Year,UserID
";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause, OrderBy);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable CalcUserPerformance(string WhereClause)
        {
            string sqlSb = @"
select sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable,SUM(isnull(c1.AccountPayableEx,0)) as AccountPayableEx
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
group by  cCusCode,cBVCode
) B 
left join ( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
)  A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer d on b.cCusCode = d.CustomerCode
left join Cms_Sys_User C on d.Salesman= C.UserID
where 1=1 {0}
";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);
 

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
           
            return ds.Tables[0];
        }
        
        //奖惩核销汇总
        public DataTable GetSalerCheck(int year,int month,string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select
              UserName
              ,SUM(performanceTotalMonth) as performanceTotalMonth
              ,SUM(checkAmountMonth) as checkAmountMonth
              ,SUM(MonthBalance) as  MonthBalance
              ,SUM(performanceTotalYear) as performanceTotalYear
              ,SUM(checkAmountYear) as checkAmountYear
              ,SUM(YearBalance) as  YearBalance
              ,SUM(performanceTotal) as performanceTotal
              ,SUM(checkAmount) as checkAmount
              ,SUM(Balance) as  Balance 
              from
              (
              select
	            u.UserName
	            ,isnull(a1.performanceTotalMonth,0) as performanceTotalMonth
	            ,isnull(b1.checkAmountMonth,0) as checkAmountMonth 
	            ,(isnull(a1.performanceTotalMonth,0) - isnull(b1.checkAmountMonth,0)) as MonthBalance
	            ,isnull(a2.performanceTotalYear,0) as performanceTotalYear
	            ,isnull(b2.checkAmountYear,0) as checkAmountYear
	            ,(isnull(a2.performanceTotalYear,0) - isnull(b2.checkAmountYear,0)) as YearBalance
	            ,isnull(a3.performanceTotal,0) as performanceTotal
	            ,isnull(b3.checkAmount,0) as checkAmount
	            ,(isnull(a3.performanceTotal,0) - isnull(b3.checkAmount,0)) as Balance
              from Cms_Sys_User u
              inner join Cms_Ctrt_Customer c  on c.Salesman = u.UserID
              left join 
	            (select userid,sum(Fine+Reward) as performanceTotalMonth from Cms_Arm_UserPerformance 
	            where [year] = {0} and [month] ={1} group by userid) a1 on a1.UserID=u.UserID
              left join 
	            (select userid,sum(Fine+Reward) as performanceTotalYear from Cms_Arm_UserPerformance 
	            where [year] = {0} group by userid) a2 on a2.UserID=u.UserID
              left join 
	            (select userid,sum(Fine+Reward) as performanceTotal from Cms_Arm_UserPerformance 
	            group by userid) a3 on a3.UserID=u.UserID
              left join 
	            (select userid,sum(RealizeFunds) as checkAmountMonth from Cms_Arm_CheckOffsForMonth 
	            where [year] = {0} and [month] ={1} group by userid) b1 on b1.UserID=u.UserID
              left join 
	            (select userid,sum(RealizeFunds) as checkAmountYear from Cms_Arm_CheckOffsForMonth 
	            where [year] = {0} group by userid) b2 on b2.UserID=u.UserID
              left join 
	            (select userid,sum(RealizeFunds) as checkAmount from Cms_Arm_CheckOffsForMonth 
	            group by userid) b3 on b3.UserID=u.UserID
	            ) s 
            where 1=1 {2}
            group by UserName";

            string sqlStr = string.Format(sqlSb.ToString(), year,month, WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable getCheckOffsListByMonthAndYear(string WhereClause, int PageSize, int PageIndex, out int RecordCount)
        {
            String sqlSb = " select isnull(sum(UnrealizedFunds),0) as UnrealizedFundsTotal,  isnull(sum(RealizeFunds),0) as RealizeFundsTotal,Month,Year,UserID from Cms_Arm_CheckOffsForMonth where 1=1 {0} group by Month,Year,UserID ";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Convert(int,Year+Month) desc ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //导出
        //按客户
        public DataTable GetCustomerPerformance_export(string WhereClause, Guid UserID)
        {
            string sqlSb = @"select CustomerName as [客户名称]
,Age as [账龄] , iNatSum as [开票总额]
, iExchSum as [累计收款额]
,AccountPayableEx as [累计收款额-逾期]
, AccountPayable as [剩余应收款], Reward as [奖励]
,Fine as [罚息] from (
 select b.cCusCode,c.CustomerName
,max(vw.Age) as Age,sum(isnull(A.Reward,0)) as Reward
,sum(isnull(A.Fine,0)) as Fine  ,sum(b.iNatSum) as iNatSum
,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable
,SUM(isnull(c1.AccountPayableEx,0)) as AccountPayableEx
from (
select cCusCode,cBVCode,sum(iNatSum) as iNatSum,sum(iExchSum) as iExchSum,sum(iNatSum-iExchSum) as AccountPayable
from Cms_Ctrt_Bill
group by  cCusCode,cBVCode
) B 
left join 
( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer c on b.cCusCode = c.CustomerCode
left join vw_CustomerAge vw on vw.CustomerID = c.CustomerID
where c.Salesman = '{0}'
group by b.cCusCode,c.CustomerName
) AS Z where 1=1 {1}";
            string sqlStr = string.Format(sqlSb.ToString(), UserID, WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable GetCustomerPerformance_export(string WhereClause, string ufAccountNo)
        {
            string sqlSb = @"select CustomerName as [客户名称],UserName as [销售员],Age as [账龄]
, iNatSum as [开票总额], iExchSum as [累计收款额], AccountPayable as [剩余应收款],AccountPayableEx as [剩余应收款-逾期], Reward as [奖励],Fine as [罚息]  from (
select b.cCusCode,c.CustomerName,u.userName,max(vw.Age) as Age,sum(isnull(A.Reward,0)) as Reward,sum(isnull(A.Fine,0)) as Fine 
,sum(b.iNatSum) as iNatSum,sum(b.iExchSum) as iExchSum,sum(b.iNatSum-b.iExchSum) as AccountPayable,SUM(isnull(c1.AccountPayableEx,0)) as AccountPayableEx
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
where cBVCode like '%{0}'
group by  cCusCode,cBVCode
) B 
left join
( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer c on b.cCusCode = c.CustomerCode
left join Cms_Sys_User u on u.UserID = c.Salesman
left join vw_CustomerAge vw on vw.CustomerID = c.CustomerID
group by b.cCusCode,c.CustomerName,u.userName
) AS Z where 1=1 {1}";
            string sqlStr = string.Format(sqlSb.ToString(), ufAccountNo, WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        //按发票
        public DataTable GetBillPerformance_export(string WhereClause, string OrderBy)
        {
            string sqlSb = @"select cBVCode as [发票号],convert(char(10),dDate,121) as [日期]
, iNatSum as [开票总额], iExchSum as [累计收款额], AccountPayable as [剩余应收款], Yearmonth as [奖惩月份] ,Reward as [奖励],Fine as [罚息] from (
select a.cCuscode,a.cBVCode,a.dDate,a.iNatSum,a.iExchSum,isnull(b.Fine,0) as Fine,isnull(b.Reward,0) as Reward,b.Year,b.Month,cast(b.Year as varchar(4))+'-'+cast(b.Month as varchar(2)) as Yearmonth,a.AccountPayable as AccountPayable from 
(select cCuscode,cBVCode,dDate,sum(iNatSum) as iNatSum,sum(iExchSum) as iExchSum,sum(iNatSum-iExchSum) as AccountPayable from Cms_Ctrt_Bill  where 1=1 {0} group by cCuscode,cBVCode,dDate) a 
left join Cms_Arm_UserPerformance b
on a.cBVCode = b.cBVCode
) AS Z where 1=1 {0} order by {1} Asc";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause,OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        //按销售员看开票
        public DataTable QueryUserPerformance_export(string WhereClause, string OrderBy)
        {
            string sqlSb = @"
select C.UserName as [销售员],sum(b.iNatSum) as [开票总额],sum(b.iExchSum) as [累计收款额],sum(b.iNatSum-b.iExchSum) as [剩余应收款],SUM(isnull(c1.AccountPayableEx,0)) as [剩余应收款-逾期]
, sum(isnull(A.Reward,0)) as [奖励],sum(isnull(A.Fine,0)) as [惩罚] 
from (
select cCusCode,cBVCode,sum(case when IsOldBalance='1' then 0 else iNatSum end) as iNatSum,sum(case when IsOldBalance='1' then 0 else iExchSum end) as iExchSum,sum(case when IsOldBalance='1' then 0 else iNatSum end - case when IsOldBalance='1' then 0 else iExchSum end) as AccountPayable
from Cms_Ctrt_Bill
group by  cCusCode,cBVCode
) B 
left join ( select  cBVCode,sum(isnull(Reward,0)) as Reward,sum(isnull(Fine,0)) as Fine
from Cms_Arm_UserPerformance group by cBVCode
) A on A.cBVCode = B.cBVCode
left join
(
select cBVCode,sum(iNatSum-iExchSum) as AccountPayableEx
from Cms_Ctrt_Bill where cBVCode in
(
select cBVCode from Cms_Arm_UserPerformance where Fine < 0 
)
group by  cBVCode
) C1 on c1.cBVCode = B.cBVCode
inner join Cms_Ctrt_Customer d on b.cCusCode = d.CustomerCode
left join Cms_Sys_User C on d.Salesman= C.UserID
where 1=1 {0}
group by C.UserName,C.UserID
order by {1}
";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause, OrderBy);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable ExportCustomerAgeStat(string WhereClause)
        {
            string sqlSb = @"select cCusCode as [客户编号],CustomerName as [客户名称],age as [设置账龄],avgAge as [当前平均账龄],suggestAge as [建议账龄] from vw_CustomerAgeStat where 1=1 {0}";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        //按销售员看奖惩核销
        public DataTable QueryUserCheckOff_export(string WhereClause, int iyear,int imonth)
        {
            string sqlSb = @"
select
a.Username as [销售员]
,isnull(b.fine,0) as [当月惩罚]
,isnull(b.reward,0) as [当月奖励]
,isnull(b.performance,0) as [当月奖惩]
,isnull(c.checkOff,0) as [当月核销]
,(isnull(b.performance,0) - isnull(c.checkOff,0)) as [当月核销余额]
,isnull(d.performance,0) as [截止当月奖惩]
,isnull(e.checkOff,0) as [截止当月核销]
,(isnull(d.performance,0) - isnull(e.checkOff,0)) as [截止当月核销余额]
from
( 
select UserID,UserName from Cms_Sys_User with(nolock)
where  IsSaleor = 1 and userid in(select salesman from Cms_Ctrt_Customer with(nolock))
) a
left join
(
select UserID,SUM(reward+fine) as performance,SUM(fine) as fine ,SUM(reward) as reward from Cms_Arm_UserPerformance with(nolock) where [Year] = {1} and [MONTH] = {2} group by UserID
) b on b.UserID = a.UserID
left join
(
select UserID,SUM(RealizeFunds)  as checkOff from Cms_Arm_CheckOffsForMonth with(nolock) where [Year] = {1} and [MONTH] = {2} group by UserID
) c on c.UserID = a.UserID
left join
(
select UserID,SUM(reward+fine) as performance from Cms_Arm_UserPerformance with(nolock) where [Year] <= {1} and [MONTH] <= {2} group by UserID
) d on d.UserID = a.UserID
left join
(
select UserID,SUM(RealizeFunds)  as checkOff from Cms_Arm_CheckOffsForMonth with(nolock) where [Year] <= {1} and [MONTH] <= {2} group by UserID
) e on e.UserID = a.UserID
where 1 = 1 {0}
order by username asc
";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause, iyear,imonth);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable SaleBillsStat_export(string WhereClause)
        {
            string sqlSb = @"select BillUnit as [开票单位],BudgetUnit as [预算单位],case when SourceType = 3 then '财务调整' else DepartmentName end as [销售组],case when SourceType = 3 then '财务调整' else sales end as [销售员],CustomerName as [客户名称],FactoryName as [品牌],categoryName as [产品小类],sum(iNatSum)  as [销售总金额]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '01' then iNatSum end,0))  as [1月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '02' then iNatSum end,0))  as [2月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '03' then iNatSum end,0))  as [3月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '04' then iNatSum end,0))  as [4月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '05' then iNatSum end,0))  as [5月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '06' then iNatSum end,0))  as [6月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '07' then iNatSum end,0))  as [7月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '08' then iNatSum end,0))  as [8月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '09' then iNatSum end,0))  as [9月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '10' then iNatSum end,0))  as [10月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '11' then iNatSum end,0))  as [11月]
,sum(isnull(case substring(CONVERT(char(10),ddate,121),6,2) when '12' then iNatSum end,0))  as [12月]
from vw_BG_Bill 
where iNatSum<>0 {0}
group by BillUnit,BudgetUnit,DepartmentName,sales,CustomerName,FactoryName,categoryName,SourceType";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable SaleBillsStat1_export(string WhereClause)
        {
            string sqlSb = @"select BillUnit as [开票单位],BudgetUnit as [预算单位],DepartmentName as [销售组],sales as [销售员],CustomerName as [客户名称],FactoryName as [品牌],categoryNameEx as [产品小类]
	                        ,(m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum) as [总计收入]
	                        ,m01iNatSum as [1月]
	                        ,m02iNatSum as [2月]
	                        ,m03iNatSum as [3月]
	                        ,m04iNatSum as [4月]
	                        ,m05iNatSum as [5月]
	                        ,m06iNatSum as [6月]
	                        ,m07iNatSum as [7月]
	                        ,m08iNatSum as [8月]
	                        ,m09iNatSum as [9月]
	                        ,m10iNatSum as [10月]
	                        ,m11iNatSum as [11月]
	                        ,m12iNatSum as [12月]
	                        ,(m01Cost+m02Cost+m03Cost+m04Cost+m05Cost+m06Cost+m07Cost+m08Cost+m09Cost+m10Cost+m11Cost+m12Cost) as [总计成本]
	                        ,m01Cost as [1月]
	                        ,m02Cost as [2月]
	                        ,m03Cost as [3月]
	                        ,m04Cost as [4月]
	                        ,m05Cost as [5月]
	                        ,m06Cost as [6月]
	                        ,m07Cost as [7月]
	                        ,m08Cost as [8月]
	                        ,m09Cost as [9月]
	                        ,m10Cost as [10月]
	                        ,m11Cost as [11月]
	                        ,m12Cost as [12月]
	                        , (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum
	                        -(m01Cost+m02Cost+m03Cost+m04Cost+m05Cost+m06Cost+m07Cost+m08Cost+m09Cost+m10Cost+m11Cost+m12Cost)) as [总计毛利]
	                        ,(m01iNatSum - m01Cost) as [1月]
	                        ,(m02iNatSum - m02Cost) as [2月]
	                        ,(m03iNatSum - m03Cost) as [3月]
	                        ,(m04iNatSum - m04Cost) as [4月]
	                        ,(m05iNatSum - m05Cost) as [5月]
	                        ,(m06iNatSum - m06Cost) as [6月]
	                        ,(m07iNatSum - m07Cost) as [7月]
	                        ,(m08iNatSum - m08Cost) as [8月]
	                        ,(m09iNatSum - m09Cost) as [9月]
	                        ,(m10iNatSum - m10Cost) as [10月]
	                        ,(m11iNatSum - m11Cost) as [11月]
	                        ,(m12iNatSum - m12Cost) as [12月]
	                        ,case when (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum)!=0
			                        then (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum
	                        -(m01Cost+m02Cost+m03Cost+m04Cost+m05Cost+m06Cost+m07Cost+m08Cost+m09Cost+m10Cost+m11Cost+m12Cost)) / (m01iNatSum+m02iNatSum+m03iNatSum+m04iNatSum+m05iNatSum+m06iNatSum+m07iNatSum+m08iNatSum+m09iNatSum+m10iNatSum+m11iNatSum+m12iNatSum)
	                        else 0 end as [总计毛利率]
	                        ,case when m01iNatSum!=0 then (m01iNatSum - m01Cost) / m01iNatSum else 0 end as [1月]
	                        ,case when m02iNatSum!=0 then (m02iNatSum - m02Cost) / m02iNatSum else 0 end as [2月]
	                        ,case when m03iNatSum!=0 then (m03iNatSum - m03Cost) / m03iNatSum else 0 end as [3月]
	                        ,case when m04iNatSum!=0 then (m04iNatSum - m04Cost) / m04iNatSum else 0 end as [4月]
	                        ,case when m05iNatSum!=0 then (m05iNatSum - m05Cost) / m05iNatSum else 0 end as [5月]
	                        ,case when m06iNatSum!=0 then (m06iNatSum - m06Cost) / m06iNatSum else 0 end as [6月]
	                        ,case when m07iNatSum!=0 then (m07iNatSum - m07Cost) / m07iNatSum else 0 end as [7月]
	                        ,case when m08iNatSum!=0 then (m08iNatSum - m08Cost) / m08iNatSum else 0 end as [8月]
	                        ,case when m09iNatSum!=0 then (m09iNatSum - m09Cost) / m09iNatSum else 0 end as [9月]
	                        ,case when m10iNatSum!=0 then (m10iNatSum - m10Cost) / m10iNatSum else 0 end as [10月]
	                        ,case when m11iNatSum!=0 then (m11iNatSum - m11Cost) / m11iNatSum else 0 end as [11月]
	                        ,case when m12iNatSum!=0 then (m12iNatSum - m12Cost) / m12iNatSum else 0 end as [12月]
                        from
                        (
                        select BillUnit,BudgetUnit,case when SourceType = 3 then '财务调整' else DepartmentName end as DepartmentName,case when SourceType = 3 then '财务调整' else sales end as sales,CustomerName,FactoryName,categoryNameEx,CategoryCode
                        ,sum(isnull(case billMonthex when 1 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m01iNatSum
                        ,sum(isnull(case billMonthex when 2 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m02iNatSum
                        ,sum(isnull(case billMonthex when 3 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m03iNatSum
                        ,sum(isnull(case billMonthex when 4 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m04iNatSum
                        ,sum(isnull(case billMonthex when 5 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m05iNatSum
                        ,sum(isnull(case billMonthex when 6 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m06iNatSum
                        ,sum(isnull(case billMonthex when 7 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m07iNatSum
                        ,sum(isnull(case billMonthex when 8 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m08iNatSum
                        ,sum(isnull(case billMonthex when 9 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m09iNatSum
                        ,sum(isnull(case billMonthex when 10 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m10iNatSum
                        ,sum(isnull(case billMonthex when 11 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m11iNatSum
                        ,sum(isnull(case billMonthex when 12 then SaleAmount+SaleAmount2+SaleAmount3+SaleAmount4 else 0 end,0))  as m12iNatSum
                        ,sum(isnull(case billMonthex when 1 then Cost+Cost2+Cost3 else 0 end,0))  as m01Cost
                        ,sum(isnull(case billMonthex when 2 then Cost+Cost2+Cost3 else 0 end,0))  as m02Cost
                        ,sum(isnull(case billMonthex when 3 then Cost+Cost2+Cost3 else 0 end,0))  as m03Cost
                        ,sum(isnull(case billMonthex when 4 then Cost+Cost2+Cost3 else 0 end,0))  as m04Cost
                        ,sum(isnull(case billMonthex when 5 then Cost+Cost2+Cost3 else 0 end,0))  as m05Cost
                        ,sum(isnull(case billMonthex when 6 then Cost+Cost2+Cost3 else 0 end,0))  as m06Cost
                        ,sum(isnull(case billMonthex when 7 then Cost+Cost2+Cost3 else 0 end,0))  as m07Cost
                        ,sum(isnull(case billMonthex when 8 then Cost+Cost2+Cost3 else 0 end,0))  as m08Cost
                        ,sum(isnull(case billMonthex when 9 then Cost+Cost2+Cost3 else 0 end,0))  as m09Cost
                        ,sum(isnull(case billMonthex when 10 then Cost+Cost2+Cost3 else 0 end,0))  as m10Cost
                        ,sum(isnull(case billMonthex when 11 then Cost+Cost2+Cost3 else 0 end,0))  as m11Cost
                        ,sum(isnull(case billMonthex when 12 then Cost+Cost2+Cost3 else 0 end,0))  as m12Cost
                        from vw_BG_Bill 
                        where 1=1 {0}
                        group by BillUnit,BudgetUnit,DepartmentName,sales,CustomerName,FactoryName,categoryNameEx,SourceType,CategoryCode
                        ) s";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable QuerySalesBills_export(string WhereClause)
        {
            string sqlSb = @"select case when a.Sourcetype=3 then '手工录人' else '发票导入' end [数据来源]
,a.cBVCode as [发票编号],a.dDate as [发票日期],c.customerName as [客户姓名],a.cMemo as [备注]
,cc.CategoryName as [存货大类],a.instrumentName as [存货名称],a.instrumentStandard as [规格],a.iQuantity as [数量],a.iTaxUnitPrice as [含税单价],a.iNatMoney as [金额],a.iNatSum as [价税合计] from Cms_BG_Bill a
inner join vw_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=left(a.cCategoryCode,6)
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable QuerySalesBills1_export(string WhereClause)
        {
            string sqlSb = @"select billUnit as [开票单位],BudgetUnit as [预算单位],Sales as [销售员],DepartmentName as [销售组],case when Sourcetype=3 then '手工录人' else '发票导入' end [数据来源]
,cBVCode as [发票编号],dDate as [发票日期],customerName as [客户姓名],cMemo as [备注]
,CategoryName as [存货大类],cInvCode as [存货编号],instrumentName as [存货名称],instrumentStandard as [存货规格],iQuantity as [数量],iTaxUnitPrice as [含税单价],iNatMoney as [金额],iNatSum as [价税合计] 
from vw_BG_Bill_query 
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable QuerySalesBills2_export(string WhereClause)
        {
            string sqlSb = @"select billUnit as [开票单位],BudgetUnit as [预算单位],Sales as [销售员],DepartmentName as [销售组],case when Sourcetype=3 then '手工录人' else '发票导入' end [数据来源]
,cBVCode as [发票编号],dDate as [发票日期],customerName as [客户姓名],SalesModelType as [销售模式],SalesType as [销售方式],cMemo as [备注]
,CategoryName as [存货大类],instrumentName as [存货名称],iQuantity as [数量],iTaxUnitPrice as [含税单价],iNatMoney as [金额],iNatSum as [价税合计] 
,(SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) as [收入],(Cost+Cost3+Cost2) as [成本],((SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4)-(Cost+Cost2+Cost3)) as [毛利]
,case when (SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) <> 0 then ((SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4)-(Cost+Cost2+Cost3))/(SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) else 0 end as [毛利率]
,SaleAmount as [收0],Cost as [成0] ,(SaleAmount-Cost) as [毛利0],case when SaleAmount <> 0 then (SaleAmount-Cost)/SaleAmount else 0 end as [毛利率0]
,SaleAmount2 as [收2],SaleAmount3 as [收3],SaleAmount4 as [收4],Cost2 as [成2],Cost3 as [成3]
from vw_BG_Bill_query 
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable QueryAssociatedBudget_export(string WhereClause)
        {
            string sqlSb = @"select a.BillUnit as [开票单位],c1.CustomerCode as [开票客户编号],c1.CustomerName as [开票客户名称]
            ,ci.categoryName as [开票存货大类],c2.CustomerCode as [预算客户编号],c2.CustomerName as [预算客户名称],ci1.categoryName as [预算存货大类]
            ,a.Sales as [销售责任人],a.OperatorName as [关联人],a.recorddate as [关联时间]
            from BudgetUnitRef a
            inner join vw_Customer c1 on c1.CustomerCode = a.CustomerCode and c1.company= a.BillUnit
            inner join vw_Customer c2 on (c2.CustomerCode = a.BudgetCustomerCode and a.BudgetCustomerCode='xzkh') or (c2.CustomerCode = a.BudgetCustomerCode and c2.company= a.BudgetUnit)
            inner join Cms_Ctrt_InstrumentCategory ci on ci.categoryCode = a.categoryCode
            inner join Cms_Ctrt_InstrumentCategory ci1 on ci1.categoryCode = a.BudgetCategoryCode where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }


        public DataTable SalesBills_export(string WhereClause)
        {
            string sqlSb = @"select a.billUnit as [开票单位],a.BudgetUnit as [预算单位],c.customerName as [客户]
            ,cc.CategoryName as [存货大类],a.cBVCode as [发票编号],a.dDate as [开票日期],a.cMemo as [摘要] ,a.iNatSum as [金额] from Cms_BG_Bill a
inner join vw_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=left(a.cCategoryCode,6)
where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable BGInstrument_export(string WhereClause)
        {
            string sqlSb = @"select
                a.CompanyName as [公司]
                ,a.InstrumentCode as [公司存货编号]
                ,a.InstrumentName as [公司存货名称]
                ,a.InstrumentStd as [公司存货规格]
                ,a.CategoryCode as [存货大类编号]
                ,cc.CategoryName as [存货大类名称]
                ,a.StandardInstrumentCode as [标准存货编号]
                ,a.StandardInstrumentName as [标准存货名称]
                ,a.StandardInstrumentStd as [标准存货规格]
                from cms_BG_Instrument a left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=a.CategoryCode
                 where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }


        public DataTable GetSalesZKBills_export(string WhereClause, string OrderBy)
        {
            string sqlSb = @"
select customerName as [客户],cBVCode as [发票编号],dDate as [开票日期],sum(iNatSum)  as [折扣金额] from (select a.*,c.customerName from vw_Salebills_Zk a
inner join vw_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
where 1=1 {0}) s group by customerName,cBVCode,dDate
order by {1}
";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause, OrderBy);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }



        public DataTable CompanyInstrumentList_export(string WhereClause)
        {
            string sqlSb = @"select CompanyName as [公司]
                ,categoryname as [存货大类]
                ,InstrumentCode as [存货编号]
                ,InstrumentName as [存货名称]
                ,InstrumentStd as [存货规格]
                ,dDate as [首次开票日]
                ,CostImport  as [导入单价]
                from 
                (
                select c.CompanyName,c.InstrumentCode,c.InstrumentName,c.InstrumentStd,c.CategoryCode,cc.categoryName,a.CostImport,c.StandardInstrumentCode,c.StandardInstrumentName,c.StandardInstrumentStd,s.CostLow,st.dDate  from cms_BG_Instrument c
                inner join Cms_Ctrt_InstrumentCategory cc on cc.categoryCode = c.CategoryCode
                left join cms_BG_companyInstrumentCost a on c.InstrumentCode = a.InstrumentCode and c.CompanyName = a.CompanyName
                left join 
                (
	                select a.StandardInstrumentCode,min(ISNULL(b.CostImport,0)) as CostLow
	                from cms_BG_Instrument a
	                left join cms_BG_companyInstrumentCost b on a.InstrumentCode = b.InstrumentCode and a.CompanyName= b.CompanyName
                    where ISNULL(b.CostImport,0)!=0
	                group by StandardInstrumentCode
                ) s on s.StandardInstrumentCode = c.StandardInstrumentCode
                left join
                (
                     select BillUnit,cInvCode,MIN(ddate) as ddate from Cms_BG_Bill group by BillUnit,cInvCode
                ) st on st.BillUnit = c.CompanyName and cInvCode = c.InstrumentCode
                ) s
            where 1=1 {0}";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable InstrumentList_export(string sYear,string WhereClause)
        {
            string sqlSb = @"select 
                CategoryName as 存货大类
                ,StandardInstrumentCode as  [存货编号]
                ,StandardInstrumentName as  [存货名称]
                ,StandardInstrumentStd  as  [存货规格]
                ,CostInit_rd    as  [润达]
                ,CostInit_kx    as  [康祥]
                ,CostInit_yx    as  [益信]
                ,CostInit_hz    as  [惠中]
                ,CostInit_hc    as  [华臣]
                ,CostLow    as  [最低价]
                ,instrumentCostAdjust_m01   as [调整 | 1月]
                ,instrumentCost_m01         as [应用 | 1月]
                ,instrumentCostAdjust_m02   as [调整 | 2月]
                ,instrumentCost_m02         as [应用 | 2月]
                ,instrumentCostAdjust_m03   as [调整 | 3月]
                ,instrumentCost_m03         as [应用 | 3月]
                ,instrumentCostAdjust_m04   as [调整 | 4月]
                ,instrumentCost_m04         as [应用 | 4月]
                ,instrumentCostAdjust_m05   as [调整 | 5月]     
                ,instrumentCost_m05         as [应用 | 5月]
                ,instrumentCostAdjust_m06   as [调整 | 6月]
                ,instrumentCost_m06         as [应用 | 6月]
                ,instrumentCostAdjust_m07   as [调整 | 7月]
                ,instrumentCost_m07         as [应用 | 7月]
                ,instrumentCostAdjust_m08   as [调整 | 8月]
                ,instrumentCost_m08         as [应用 | 8月]
                ,instrumentCostAdjust_m09   as [调整 | 9月]
                ,instrumentCost_m09         as [应用 | 9月]
                ,instrumentCostAdjust_m10   as [调整 | 10月]
                ,instrumentCost_m10         as [应用 | 10月]
                ,instrumentCostAdjust_m11   as [调整 | 11月]
                ,instrumentCost_m11         as [应用 | 11月]
                ,instrumentCostAdjust_m12   as [调整 | 12月]
                ,instrumentCost_m12         as [应用 | 12月]
                from
                (
                select cc.CategoryName
                ,s1.*,s2.CostLow,s3.* from
                (
                select
                StandardInstrumentCode
                ,StandardInstrumentName
                ,StandardInstrumentStd
                ,CategoryCode
                ,MAX(CostInit_rd) as CostInit_rd
                ,MAX(CostInit_kx) as CostInit_kx
                ,MAX(CostInit_yx) as CostInit_yx
                ,MAX(CostInit_hz) as CostInit_hz
                ,MAX(CostInit_hc) as CostInit_hc
                from
                (
                select a.StandardInstrumentCode
                ,a.StandardInstrumentName
                ,a.StandardInstrumentStd
                ,a.CategoryCode
                ,case when b.CompanyName='润达' then b.CostImport else null end as CostInit_rd 
                ,case when b.CompanyName='康祥' then b.CostImport else null end as CostInit_kx 
                ,case when b.CompanyName='益信' then b.CostImport else null end as CostInit_yx
                ,case when b.CompanyName='惠中' then b.CostImport else null end as CostInit_hz
                ,case when b.CompanyName='华臣' then b.CostImport else null end as CostInit_hc
                from cms_BG_Instrument a
                left join cms_BG_companyInstrumentCost b on a.InstrumentCode = b.InstrumentCode and a.CompanyName= b.CompanyName
                ) s group by StandardInstrumentCode,StandardInstrumentName,StandardInstrumentStd,CategoryCode
                ) s1
                inner join Cms_Ctrt_InstrumentCategory cc on cc.categoryCode = s1.CategoryCode
                left join 
                (
                select a.StandardInstrumentCode,min(ISNULL(b.CostImport,0)) as CostLow
                from cms_BG_Instrument a
                left join cms_BG_companyInstrumentCost b on a.InstrumentCode = b.InstrumentCode and a.CompanyName= b.CompanyName
                where ISNULL(b.CostImport,0)!=0
                group by StandardInstrumentCode 
                ) s2 on s1.StandardInstrumentCode = s2.StandardInstrumentCode
                left join
                (
                select InstrumentCode
                ,sum(case when Updmonth=1 then Cost else 0 end) instrumentCost_m01
                ,sum(case when Updmonth=2 then Cost else 0 end) instrumentCost_m02
                ,sum(case when Updmonth=3 then Cost else 0 end) instrumentCost_m03
                ,sum(case when Updmonth=4 then Cost else 0 end) instrumentCost_m04
                ,sum(case when Updmonth=5 then Cost else 0 end) instrumentCost_m05
                ,sum(case when Updmonth=6 then Cost else 0 end) instrumentCost_m06
                ,sum(case when Updmonth=7 then Cost else 0 end) instrumentCost_m07
                ,sum(case when Updmonth=8 then Cost else 0 end) instrumentCost_m08
                ,sum(case when Updmonth=9 then Cost else 0 end) instrumentCost_m09
                ,sum(case when Updmonth=10 then Cost else 0 end) instrumentCost_m10
                ,sum(case when Updmonth=11 then Cost else 0 end) instrumentCost_m11
                ,sum(case when Updmonth=12 then Cost else 0 end) instrumentCost_m12
                ,sum(case when Updmonth=1 then CostAdjust else 0 end) instrumentCostAdjust_m01
                ,sum(case when Updmonth=2 then CostAdjust else 0 end) instrumentCostAdjust_m02
                ,sum(case when Updmonth=3 then CostAdjust else 0 end) instrumentCostAdjust_m03
                ,sum(case when Updmonth=4 then CostAdjust else 0 end) instrumentCostAdjust_m04
                ,sum(case when Updmonth=5 then CostAdjust else 0 end) instrumentCostAdjust_m05
                ,sum(case when Updmonth=6 then CostAdjust else 0 end) instrumentCostAdjust_m06
                ,sum(case when Updmonth=7 then CostAdjust else 0 end) instrumentCostAdjust_m07
                ,sum(case when Updmonth=8 then CostAdjust else 0 end) instrumentCostAdjust_m08
                ,sum(case when Updmonth=9 then CostAdjust else 0 end) instrumentCostAdjust_m09
                ,sum(case when Updmonth=10 then CostAdjust else 0 end) instrumentCostAdjust_m10
                ,sum(case when Updmonth=11 then CostAdjust else 0 end) instrumentCostAdjust_m11
                ,sum(case when Updmonth=12 then CostAdjust else 0 end) instrumentCostAdjust_m12
                from cms_BG_InstrumentCost where UpdYear ={0}
                group by InstrumentCode
                ) s3 on s1.StandardInstrumentCode = s3.InstrumentCode
            ) s
            where 1=1 {1}";

            string sqlStr = string.Format(sqlSb.ToString(), sYear, WhereClause);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public string UpdateInstrumentCost(int iYear,int iMonth)
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("UpdateInstrumentCostByInit");
            CurrentDatabase.AddInParameter(dbcommand, "@iyear", DbType.Int16, iYear);
            CurrentDatabase.AddInParameter(dbcommand, "@imonth", DbType.Int16, iMonth);
            CurrentDatabase.AddOutParameter(dbcommand, "@msg", DbType.String, 50);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
            return dbcommand.Parameters["@msg"].Value.ToString();
        }
        public string settleAccount(int iYear, int iMonth)
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("CloseAccount");
            dbcommand.CommandTimeout = 0;
            
            CurrentDatabase.AddInParameter(dbcommand, "@iYear", DbType.Int16, iYear);
            CurrentDatabase.AddInParameter(dbcommand, "@iMonth", DbType.Int16, iMonth);
            CurrentDatabase.AddOutParameter(dbcommand, "@msg", DbType.String, 50);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
            return dbcommand.Parameters["@msg"].Value.ToString();
        }

        public string CheckBalance(Guid CheckID, decimal RevenueByManual, decimal CostByManual)
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("CheckBalance");
            CurrentDatabase.AddInParameter(dbcommand, "@CheckID", DbType.Guid, CheckID);
            CurrentDatabase.AddInParameter(dbcommand, "@RevenueByManual", DbType.Decimal, RevenueByManual);
            CurrentDatabase.AddInParameter(dbcommand, "@CostByManual", DbType.Decimal, CostByManual);
            CurrentDatabase.AddOutParameter(dbcommand, "@msg", DbType.String, 50);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
            return dbcommand.Parameters["@msg"].Value.ToString();
        }

        public string UpdateRptData(Guid CheckID, int iYear,int iMonth, decimal RevenueByManual, decimal CostByManual, decimal SaleAmount, decimal Cost)
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("apportionRptData");
            CurrentDatabase.AddInParameter(dbcommand, "@CheckID", DbType.Guid, CheckID);
            CurrentDatabase.AddInParameter(dbcommand, "@iYear", DbType.Int16, iYear);
            CurrentDatabase.AddInParameter(dbcommand, "@iMonth", DbType.Int16, iMonth);
            CurrentDatabase.AddInParameter(dbcommand, "@RevenueByManual", DbType.Decimal, RevenueByManual);
            CurrentDatabase.AddInParameter(dbcommand, "@CostByManual", DbType.Decimal, CostByManual);
            CurrentDatabase.AddInParameter(dbcommand, "@SaleAmount", DbType.Decimal, SaleAmount);
            CurrentDatabase.AddInParameter(dbcommand, "@Cost", DbType.Decimal, Cost);
            CurrentDatabase.AddOutParameter(dbcommand, "@msg", DbType.String, 50);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
            return dbcommand.Parameters["@msg"].Value.ToString();
        }

        public void checkRptData(int iYear, int iMonth)
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("CheckRptData");
            CurrentDatabase.AddInParameter(dbcommand, "@iYear", DbType.Int16, iYear);
            CurrentDatabase.AddInParameter(dbcommand, "@iMonth", DbType.Int16, iMonth);
            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }
        //确认存货档案信息时，更新发票中的存货大类（主要考虑到益信手工导入发票时，可能会发现发票中存货大类编号有误）
        public int updateBillCategoryCode(string sCompanyname ,string sInstrumentCode,string sCategoryCode)
        {
            string sqlSb = @"update cms_bg_bill set cCategoryCode='{2}' where billUnit = '{0}' and cInvCode = '{1}'";

            string sqlStr = string.Format(sqlSb.ToString(), sCompanyname, sInstrumentCode, sCategoryCode);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        //更新分摊状态

        public int updateCheckStatus(int iYear, int iMonth, string sStatus)
        {
            string sqlSb = @"update cms_sys_CheckRevenue set IsClose='{2}' where iYear = '{0}' and iMonth = '{1}'";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth, sStatus);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        public int cancelApportion(int iYear, int iMonth)
        {
            string sqlSb = @"update Cms_BG_Bill set iNatMoney_Adjust = 0,Cost2 = 0,Cost3=0,SaleAmount2=0,SaleAmount3=0,SaleAmount4=0 where billYear = {0} and billMonth = {1}";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        public int updatesettleStatus(int iYear, int iMonth, string sStatus)
        {
            string sqlSb = @"update cms_sys_CheckRevenue set settle='{2}' where iYear = '{0}' and iMonth = '{1}'";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth, sStatus);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        public int updateApportion1Status(int iYear, int iMonth, string sStatus)
        {
            string sqlSb = @"update cms_sys_CheckRevenue set apportion1='{2}' where iYear = '{0}' and iMonth = '{1}'";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth, sStatus);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }
        public int updateApportion2Status(int iYear, int iMonth, string sStatus)
        {
            string sqlSb = @"update cms_sys_CheckRevenue set apportion2='{2}' where iYear = '{0}' and iMonth = '{1}'";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth, sStatus);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        public int updateApportion3Status(int iYear, int iMonth, string sStatus)
        {
            string sqlSb = @"update cms_sys_CheckRevenue set apportion3='{2}' where iYear = '{0}' and iMonth = '{1}'";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth, sStatus);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        public int updateApportion1Status(string iYear, string iMonth, string sStatus)
        {
            string sqlSb = @"update cms_sys_CheckRevenue set apportion1='{2}' where iYear = '{0}' and iYear = '{iMonth}'";

            string sqlStr = string.Format(sqlSb.ToString(), iYear, iMonth, sStatus);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        //更新数据（sql）
        public int updateBill(string BVCode, string dueDate)
        {
            string sqlSb = @"update Cms_Ctrt_Bill set idDueDte='{1}' where cBVCode = '{0}'";

            string sqlStr = string.Format(sqlSb.ToString(), BVCode, dueDate);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        //更新数据（sql）
        public int Executesql(string sql)
        {
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        //删除发票
        public int DelBill(string billno)
        {
            string sqlSb = @"delete Cms_BG_Bill where cBVCode ='{0}'";

            string sqlStr = string.Format(sqlSb.ToString(), billno);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        //删除存货成本单价
        public int DelInstrumentCost(string sYear,string sMonth,string instrumentCode)
        {
            string sqlSb = @"delete cms_BG_GroupInstrumentCost where CostYear =" + sYear + " and CostMonth =" + sMonth + " and instrumentCode='" + instrumentCode + "'";

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlSb);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }
        public int DelInstrumentCost(string CompanyName, string instrumentCode)
        {
            string sqlSb = @"delete cms_BG_companyInstrumentCost where companyname='" + CompanyName + "' and instrumentCode='" + instrumentCode + "'";

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlSb);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }

        //更新存货价格表
        public int updateInstrumentAdjustCost(string instrumentCode, string sYear, string sMonth, decimal cost, string UpdatedMan)
        {
            string sqlSb = @"update cms_BG_InstrumentCost set CostAdjust=" + cost + ",Cost=" + cost + ",updatetime=getdate(),Updatedman='" + UpdatedMan + "' where InstrumentCode = '{0}' and UpdYear=" + sYear + "and UpdMonth=" + sMonth;

            string sqlStr = string.Format(sqlSb.ToString(), instrumentCode);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }


        //删除已计算好数据（sql），防止发票作废引起的数据变化
        public int DelBillPerformance(int iyear, int imonth)
        {
            string sqlSb = @"delete Cms_Arm_UserPerformance where Year = {0} and month = {1}";

            string sqlStr = string.Format(sqlSb.ToString(), iyear, imonth);
            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            int irtn = CurrentDatabase.ExecuteNonQuery(dbCommand);
            return irtn;
        }
        public void SynchroBill()
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("sync_UFbills");
            dbcommand.CommandTimeout = 0;

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public int IsExistsInv(string invNO)
        {
            string sqlSb = @"select count(*) from Cms_Ctrt_Bill where cBVCode= '{0}'";
            string sqlStr = string.Format(sqlSb.ToString(),invNO);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            int RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            return RecordCount;

        }

        public int AssociatedSaleUnitnum(string sqlwhere)
        {
            string sqlSb = @"select count(*) from BudgetUnitRef where 1=1 {0}";
            string sqlStr = string.Format(sqlSb.ToString(), sqlwhere);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            int RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            return RecordCount;

        }

        public DataTable AssociatedSaleUnit(string sqlwhere)
        {
            string sqlSb = @"select a.RefGUID,a.BillUnit,b.CustomerName,c.categoryNameEx,a.OperatorName from BudgetUnitRef a
                inner join Cms_Ctrt_Customer b on a.CustomerCode = b.CustomerCode  and (b.company = a.BillUnit or b.CustomerCode='xzkh')
                inner join Cms_Ctrt_InstrumentCategory c on c.categoryCode = a.categoryCode where 1=1 {0} order by a.BillUnit,b.CustomerName";
            string sqlStr = string.Format(sqlSb.ToString(), sqlwhere);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];

        }

        public DataTable QueryInstrumentList(string whereClause,string orderby)
        {
            string sqlSb = @"select a.*,cc.CategoryName from cms_BG_Instrument a
left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=a.CategoryCode
where 1=1 {0} ORDER BY {1}";
            string sqlStr = string.Format(sqlSb.ToString(), whereClause,orderby);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
            dbCommand.CommandTimeout = 0;
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        public DataTable QueryInstrumentList(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder("select a.*,cc.CategoryName from cms_BG_Instrument a left join Cms_Ctrt_InstrumentCategory cc with(nolock) on cc.CategoryCode=a.CategoryCode where 1=1 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            dbCommand.CommandTimeout = 0;
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


        public DataTable QueryCompanyInstrumentCostList(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from 
                (
                select c.CompanyName,c.InstrumentCode,c.InstrumentName,c.InstrumentStd,c.CategoryCode,cc.categoryName,a.CostImport,c.StandardInstrumentCode,c.StandardInstrumentName,c.StandardInstrumentStd,s.CostLow,st.dDate  from cms_BG_Instrument c
                inner join Cms_Ctrt_InstrumentCategory cc on cc.categoryCode = c.CategoryCode
                left join cms_BG_companyInstrumentCost a on c.InstrumentCode = a.InstrumentCode and c.CompanyName = a.CompanyName
                left join 
                (
	                select a.StandardInstrumentCode,min(ISNULL(b.CostImport,0)) as CostLow
	                from cms_BG_Instrument a
	                left join cms_BG_companyInstrumentCost b on a.InstrumentCode = b.InstrumentCode and a.CompanyName= b.CompanyName
                    where ISNULL(b.CostImport,0)!=0
	                group by StandardInstrumentCode
                ) s on s.StandardInstrumentCode = c.StandardInstrumentCode
                left join
                (
                     select BillUnit,cInvCode,MIN(ddate) as ddate from Cms_BG_Bill group by BillUnit,cInvCode
                ) st on st.BillUnit = c.CompanyName and cInvCode = c.InstrumentCode
                ) s
            where 1=1 {0}";

            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


        public DataTable QueryInstrumentCostList(string sYear, string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select * from
                (
                select cc.CategoryName
                ,s1.*,s2.CostLow,s3.* from
                (
                select
                StandardInstrumentCode
                ,StandardInstrumentName
                ,StandardInstrumentStd
                ,CategoryCode
                ,MAX(CostInit_rd) as CostInit_rd
                ,MAX(CostInit_kx) as CostInit_kx
                ,MAX(CostInit_yx) as CostInit_yx
                ,MAX(CostInit_hz) as CostInit_hz
                ,MAX(CostInit_hc) as CostInit_hc
                from
                (
                select a.StandardInstrumentCode
                ,a.StandardInstrumentName
                ,a.StandardInstrumentStd
                ,a.CategoryCode
                ,case when b.CompanyName='润达' then b.CostImport else null end as CostInit_rd 
                ,case when b.CompanyName='康祥' then b.CostImport else null end as CostInit_kx 
                ,case when b.CompanyName='益信' then b.CostImport else null end as CostInit_yx
                ,case when b.CompanyName='惠中' then b.CostImport else null end as CostInit_hz
                ,case when b.CompanyName='华臣' then b.CostImport else null end as CostInit_hc
                from cms_BG_Instrument a
                left join cms_BG_companyInstrumentCost b on a.InstrumentCode = b.InstrumentCode and a.CompanyName= b.CompanyName
                ) s group by StandardInstrumentCode,StandardInstrumentName,StandardInstrumentStd,CategoryCode
                ) s1
                inner join Cms_Ctrt_InstrumentCategory cc on cc.categoryCode = s1.CategoryCode
                left join 
                (
                select a.StandardInstrumentCode,min(ISNULL(b.CostImport,0)) as CostLow
                from cms_BG_Instrument a
                left join cms_BG_companyInstrumentCost b on a.InstrumentCode = b.InstrumentCode and a.CompanyName= b.CompanyName
                where ISNULL(b.CostImport,0)!=0
                group by StandardInstrumentCode 
                ) s2 on s1.StandardInstrumentCode = s2.StandardInstrumentCode
                left join
                (
                select InstrumentCode
                ,sum(case when Updmonth=1 then Cost else 0 end) instrumentCost_m01
                ,sum(case when Updmonth=2 then Cost else 0 end) instrumentCost_m02
                ,sum(case when Updmonth=3 then Cost else 0 end) instrumentCost_m03
                ,sum(case when Updmonth=4 then Cost else 0 end) instrumentCost_m04
                ,sum(case when Updmonth=5 then Cost else 0 end) instrumentCost_m05
                ,sum(case when Updmonth=6 then Cost else 0 end) instrumentCost_m06
                ,sum(case when Updmonth=7 then Cost else 0 end) instrumentCost_m07
                ,sum(case when Updmonth=8 then Cost else 0 end) instrumentCost_m08
                ,sum(case when Updmonth=9 then Cost else 0 end) instrumentCost_m09
                ,sum(case when Updmonth=10 then Cost else 0 end) instrumentCost_m10
                ,sum(case when Updmonth=11 then Cost else 0 end) instrumentCost_m11
                ,sum(case when Updmonth=12 then Cost else 0 end) instrumentCost_m12
                ,sum(case when Updmonth=1 then CostAdjust else 0 end) instrumentCostAdjust_m01
                ,sum(case when Updmonth=2 then CostAdjust else 0 end) instrumentCostAdjust_m02
                ,sum(case when Updmonth=3 then CostAdjust else 0 end) instrumentCostAdjust_m03
                ,sum(case when Updmonth=4 then CostAdjust else 0 end) instrumentCostAdjust_m04
                ,sum(case when Updmonth=5 then CostAdjust else 0 end) instrumentCostAdjust_m05
                ,sum(case when Updmonth=6 then CostAdjust else 0 end) instrumentCostAdjust_m06
                ,sum(case when Updmonth=7 then CostAdjust else 0 end) instrumentCostAdjust_m07
                ,sum(case when Updmonth=8 then CostAdjust else 0 end) instrumentCostAdjust_m08
                ,sum(case when Updmonth=9 then CostAdjust else 0 end) instrumentCostAdjust_m09
                ,sum(case when Updmonth=10 then CostAdjust else 0 end) instrumentCostAdjust_m10
                ,sum(case when Updmonth=11 then CostAdjust else 0 end) instrumentCostAdjust_m11
                ,sum(case when Updmonth=12 then CostAdjust else 0 end) instrumentCostAdjust_m12
                from cms_BG_InstrumentCost where UpdYear ={0}
                group by InstrumentCode
                ) s3 on s1.StandardInstrumentCode = s3.InstrumentCode
            ) s
            where 1=1 {1}";

            string sqlStr = string.Format(sqlSb.ToString(), sYear, whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable QueryInstrumentCostList(string sYear, string sMonth, string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder("select a.companyname,a.instrumentcode,a.instrumentName,a.instrumentStd,c.categoryName,b.costimport,b.costimport as cost from cms_BG_Instrument a ");
            sqlSb.Append("inner join Cms_Ctrt_InstrumentCategory c on c.categoryCode= a.categorycode ");
            sqlSb.Append("left join cms_BG_companyInstrumentCost b on a.companyname = b.companyname and a.instrumentCode = b.instrumentCode ");
            sqlSb.Append("where 1=1 {0}");

            string sqlStr = string.Format(sqlSb.ToString(), whereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }


    }
}
